﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Reflection;
using System.Threading;

namespace SqlServerDriver
{
	/*
		Created just to break up the large driver class. This will take care of all the 
		extended functions that don't necessarily generate Sql driven datasets.
	*/ 
	class ExtensionCommandProcessor
	{
		internal ExtensionCommandProcessor(SqlServerDataSource parent)
		{
			this.dataSource = parent;
		}

		/*
			command:		the command to process (duh). Likely to contain parameter values
			object window	preserving the farce that this could work on other than WINFORMS this is a parent object
							to host a dialog.
			connectionConfig param to give the operations access to other connections for eg. copy table and quick diff
			searchText		Filter box text
			out nextCommand A command to execute if this is necessary. See QuickDiff
			Return			true if we've done something to change the grid currently displayed.
		*/
		internal bool ProcessCommand(DbView.Command command, object window, DbViewDriver.IConnectionConfig connectionConfig, String searchText, out DbView.Command nextCommand)
		{
			nextCommand = new DbView.Command(DbView.Commands.Cmnd.Nothing);
			String connectionString = this.dataSource.ConnectionString;
			System.Windows.Forms.Form parent = window as System.Windows.Forms.Form;
			// for sql command api an error message and success flag
			String script = "";
			String execMessage;
			bool isOk = false;

			switch (command.Name)
			{
				case "TEST":
				{
					if (parent != null)
						System.Windows.Forms.MessageBox.Show(parent, "Hi from test");
					return true; // try refresh
				}

				case Cmd.CopyTable:
				{
					if (CopyObject(command, CopyTableModel.CopyObjectType.Table, connectionConfig, parent))
						return true;
				}
				break;

				case Cmd.CopyView:
				{
					if (CopyObject(command, CopyTableModel.CopyObjectType.View, connectionConfig, parent))
						return true;
				}
				break;

				case Cmd.CopyProc:
				{
					if (CopyObject(command, CopyTableModel.CopyObjectType.Procedure, connectionConfig, parent))
						return true;
				}
				break;

				case Cmd.EditDescriptions:
				{
					if (parent != null)
					{
						// legacy link: from 
						String table = command["TABLE"];
						String field = command[DbView.Command.TableParameterName(0)];
						String description = command["DESC"];
						// try new
						String propertyName = command["PROP"];
						if (!String.IsNullOrEmpty(propertyName))
						{
							// new style detected. Params are stored differently
							table = command[DbView.Command.TableParameterName(0)];
							field = command["COL"];
							description = command["PROPVAL"];
						}
						using (MetaDataEditorForm metaDataEditorForm = new MetaDataEditorForm(connectionString, table, field, propertyName, description))
						{
							if (metaDataEditorForm.ShowDialog(parent) == System.Windows.Forms.DialogResult.OK)
								return true;
						}
					}
				}
				break;
			
				case Cmd.QuickDiffDialog:
				{
					if (parent != null)
					{
						// caller is used to determine the object type to set in the drop down
						// it will be set depending on which object list was right-clicked on
						String caller = command["CALLER"];
						QuickDiffDialog dlg = new QuickDiffDialog(connectionConfig, QuickDiffEngine.ObjectsTypes, caller);
						// set the compared database to the last one used (if any)
						if (this.holdCopyTargetDb.Length > 0) dlg.TargetDb = this.holdCopyTargetDb;
						// This may be obsolete as there is no Main menu quick diff
						if (this.holdObjectType.Length > 0 && caller.Length == 0) dlg.ObjectType = this.holdObjectType;
						// show dialog and proceed if OK
						if (dlg.ShowDialog(parent) == System.Windows.Forms.DialogResult.OK)
						{
							this.holdCopyTargetDb = dlg.TargetDb;  // update held selections for next time
							this.holdObjectType = dlg.ObjectType;
							// do the compare in the Engine object. connectionConfig will resolve the 
							// connection tags to proper connection strings.
							QuickDiffEngine diffEngine = new QuickDiffEngine(connectionConfig);
							// change this to a String.
							diffEngine.Configure(dlg.SourceDb, dlg.TargetDb, dlg.ObjectType);
							// before executing check whether each connection has sufficient permissions
							String preExecuteCheckMessage = "";
							if (diffEngine.PreExecuteCheck(out preExecuteCheckMessage) == false)
							{
								System.Windows.Forms.MessageBox.Show(preExecuteCheckMessage, "Cannot proceed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
							}
							else
							{
								// The engine creates a result table which we then need to plug in to the
								// main form grid view. This is a new pattern for doing things, which has somewhat
								// bent the framework slightly. The (present) solution is to maintain a repository
								// of data-tables in a singleton and attach an ID to the command so the table follows
								// the command into history. This leaves a problem of when to clear up the tables as 
								// they are referenced by a singleton that stays alive. For now it's brushed under 
								// the carpet.
								// implement a table ID requester for this
								int tableId = DataTableRepository.Instance.SetTable(diffEngine.Execute(String.Format("Quick Diff: {0}", dlg.ObjectType)));
								// create a general purpose command 
								DbView.Command diffCommand = Cmd.MakeCommand(Cmd.DisplayHeldTable);
								// add table Id so it is stored with the command
								diffCommand.AddParam(CmdParam.TableId, tableId.ToString());
								nextCommand = diffCommand;
							}
						}
					}
				}
				break;
			
				case Cmd.BrokerBuilderDialog:
				{
					if (parent != null)
					{
						BrokerObjectCollection bo = new BrokerObjectCollection();
						bo.Populate(connectionString);
						BrokerObjectAddForm brokerBuilderDialog = new BrokerObjectAddForm(bo);
						// invoke the dialog here
						brokerBuilderDialog.ShowDialog(parent);
						return false;
					}
				}
				break;

				case Cmd.LaunchProfiler:
				{
					//TryLaunchProfiler(parent);
					this.TryShowProfiler(parent);
				}
				break;
				case Cmd.ScriptClass:
				{
					String table = command[DbView.Command.TableParameterName(0)];
					// context for the dialog
					using (ClassBuilderForm  form = new ClassBuilderForm(table, this.dataSource))
					{
						form.ShowDialog(parent);
					}
				}
				break;

				case Cmd.CreateTable:
				{
					// context for the dialog
					using (CreateTableForm form = new CreateTableForm())
					{
						while (true) // keep displaying the dialog if there is an error
						{
							if (form.ShowDialog(parent) == System.Windows.Forms.DialogResult.OK)
							{
								bool createdOk;
								String result = Helper.GenericExecute(connectionString, form.Script, out createdOk);
								if (createdOk)
									return true;
								else
									System.Windows.Forms.MessageBox.Show(result + "\n" +form.Script, "Create table failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
							}
							else 
								return false;
						}
					}
				}
				case Cmd.CreateSchema:
				{
					RequestNameForm form = new RequestNameForm();
					if (form.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
						return false;
					String schema = form.ObjectName;
					String messageCaption = "Add New Schema";
					String failMessage = "Add Schema failed";
					script = String.Format("CREATE Schema {0};", Helper.SafeTableName(schema));
					return this.ExecuteWithFeedback(connectionString, script, failMessage, "", messageCaption, parent);
				}
				case Cmd.RemoveSchema:
				{
					String message = "";
					String schema = command[DbView.Command.TableParameterName(0)];
					String objectType = command["TYPE"];
					if (objectType.ToUpper() != "SCHEMA")
					{
						message = String.Format("You cannot remove a {0}, only a Schema", objectType);
						System.Windows.Forms.MessageBox.Show(parent, message, String.Format("Remove {0} not permitted", schema), System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
						return false;
					}
					message = String.Format("Confirm that you want to remove schema {0}.", schema);
					String messageCaption = "Remove Schema";
					String failMessage = "Remove Schema failed for the reason given below";
					script = String.Format("DROP Schema {0};", Helper.SafeTableName(schema));
					return this.ExecuteWithFeedback(connectionString, script, failMessage, message, messageCaption, parent);
				}
				
				case Cmd.AddFK:
				{
					String table = command["TABLE"];
					String field = command[DbView.Command.TableParameterName(0)];
					ForeignKeyForm fkeyDialog = new ForeignKeyForm(this.dataSource, table, field);
					if (fkeyDialog.ShowDialog(parent) == System.Windows.Forms.DialogResult.OK)
						return true;
				}
				break;
				case Cmd.DelFK:
				{
					String table = command[DbView.Command.TableParameterName(0)];
					String fk = command[CmdParam.FkName]; // set in TableCommandProcessor.BuildTable()
					script = String.Format("alter table {0} drop constraint {1}", Helper.SafeTableName(table), fk);
					bool createdOk;
					String result = Helper.GenericExecute(connectionString, script, out createdOk);
					if (createdOk)
						return true;
					else
						System.Windows.Forms.MessageBox.Show(result + "\n" +script, "Remove FK failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
				}
				break;
				case Cmd.DisableFK:
				{
					String table = command[DbView.Command.TableParameterName(0)];
					String fk = command[CmdParam.FkName]; // set in TableCommandProcessor.BuildTable()
					script = String.Format("alter table {0} nocheck constraint {1}", Helper.SafeTableName(table), fk);
					bool createdOk;
					String result = Helper.GenericExecute(connectionString, script, out createdOk);
					if (createdOk)
						return true;
					else
						System.Windows.Forms.MessageBox.Show(result + "\n" +script, "Disable FK failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
				}
				break;
				case Cmd.EnableFK:
				{
					String table = command[DbView.Command.TableParameterName(0)];
					String fk = command[CmdParam.FkName]; // set in TableCommandProcessor.BuildTable()
					script = String.Format("alter table {0} with check check constraint {1}", Helper.SafeTableName(table), fk);
					bool createdOk;
					String result = Helper.GenericExecute(connectionString, script, out createdOk);
					if (createdOk)
						return true;
					else
						System.Windows.Forms.MessageBox.Show(result + "\n" +script, "Enable FK failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
				}

				break;
				case Cmd.ResetAuto:
				{
					// test whether or not the table has a auto-increment key
					// find max as per script of data
					String table = command["TABLE"];
					// check the identity key
					script =String.Format(@"
select     cols.name as [Field Name]
 from sys.columns cols
where cols.object_id = OBJECT_ID('{0}') 
  and cols.is_identity = 1
", Helper.SafeTableName(table));
					DataTable dt = Helper.GenericQuery(connectionString, script);
					if (dt.Rows.Count == 0)
					{
						System.Windows.Forms.MessageBox.Show("Could not locate identity field in "+table+"\n"+script, "Reset failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
						return false;
					}
					String column = dt.Rows[0][0].ToString();
					script = SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed, table, column);
					execMessage = Helper.GenericExecute(connectionString, script, out isOk);
					if (isOk) return true;
					System.Windows.Forms.MessageBox.Show(execMessage + "\n" +script, "Reset failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
				}
				break;
			}

			if (command.Cmd == DbView.Commands.Cmnd.Droptable)
			{
				String table = command["TABLE"];
				String message = String.Format("Confirm that you want to remove table {0} from the database.", table);
				if (this.ConfirmDangerousOperation(parent, "Drop table", message))
				{
					script = String.Format("DROP TABLE {0};", Helper.SafeTableName(table));
					execMessage = Helper.GenericExecute(connectionString, script, out isOk);
					if (isOk) return true;
					System.Windows.Forms.MessageBox.Show(execMessage + "\n" +script, "Drop table failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
				}
			}

			if (command.Cmd == DbView.Commands.Cmnd.Cleartable)
			{
				//String table = command["TABLE"];
				//// TODO: add a confirm dialog
				//if (ConfirmDangerousOperation(parent, "Truncate table", message))
				//{
				//    script = "TRUNCATE TABLE ["+table+"];";
				//    execMessage = Helper.GenericExecute(connectionString, script, out opOk);
				//    if (opOk) return true;
				//    System.Windows.Forms.MessageBox.Show(execMessage + "\n" +script, "truncate table failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
				////}

				String table = command["TABLE"];
				String message = String.Format("Confirm that you want to clear all data from table {0}.", table);
				String messageCaption = "Truncate table";
				String failMessage = "Truncate table failed";
				script = String.Format("TRUNCATE TABLE {0};", Helper.SafeTableName(table));
				return this.ExecuteWithFeedback(connectionString, script, failMessage, message, messageCaption, parent);
			}

			if (command.Cmd == DbView.Commands.Cmnd.RemoveColumn)
			{
				String table = command["TABLE"];
				String column = command[DbView.Command.TableParameterName(0)];
				String message = String.Format("Confirm that you want to remove column {1} from table {0}.", table, column);
				String messageCaption = "Remove column";
				String failMessage = "remove column table failed";
				script = String.Format("ALTER TABLE {0} DROP COLUMN {1};", Helper.SafeTableName(table), Helper.SafeSqlField(column));

				return this.ExecuteWithFeedback(connectionString, script, failMessage, message, messageCaption, parent);
			}
			if (command.Cmd == DbView.Commands.Cmnd.AddColumn)
			{
				String table = command["TABLE"];
				String column = command[DbView.Command.TableParameterName(0)];
				// get 
				SqlTableSchema schema = this.dataSource.MakeSchemaFor(table);
				DbView.FieldInfo fieldInfo = ModifyColumnForm.NewField();
				ModifyColumnForm dialog = new ModifyColumnForm(table, schema, fieldInfo);
				while (true)
				{
					if (dialog.ShowDialog(parent) == System.Windows.Forms.DialogResult.Cancel)
						return false;

					script = dialog.GenerateScript(false);
				
					//String messageCaption = "Add Column";
					String failMessage = "Add column failed";
					if (this.ExecuteWithFeedback(connectionString, script, failMessage, "", "", parent))
						return true; // done ok so leave
					// here if execute errored. Redisplay dialog so user can correct or cancel.
				}
			}

			if (command.Cmd == DbView.Commands.Cmnd.ChangeColumn)
			{
				String table = command["TABLE"];
				String column = command[DbView.Command.TableParameterName(0)];
				// get 
				SqlTableSchema schema = this.dataSource.MakeSchemaFor(table);
				DbView.FieldInfo fieldInfo = schema[column];

				if (!this.CheckColumnIsOkToModify(parent, fieldInfo)) // disallow PKey, index, fkey etc.
					return false;

				ModifyColumnForm dialog = new ModifyColumnForm(table, schema, fieldInfo);
				if (dialog.ShowDialog(parent) == System.Windows.Forms.DialogResult.Cancel)
					return false;

				script = dialog.GenerateScript(false);
				
				String failMessage = "Modify column failed";
				// return ExecuteWithFeedback(connectionString, script, failMessage, "", "", parent);
				execMessage = Helper.GenericExecute(connectionString, script, out isOk);
				if (isOk) return true; // done - hurrah

				String altMessage = dialog.AltEffect();
				String message = String.Format("Could not modify column without side effects. "+
											   "The side effect is this:\n\n" +
											   "{0}\n\n" +
											   "Would you like to proceed with the change?"
											   , altMessage);
				if (System.Windows.Forms.MessageBox.Show(parent, message, "Problem with column change", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
					return false;
				// try for alt message
				script = dialog.GenerateScript(true);
				return this.ExecuteWithFeedback(connectionString, script, failMessage, "", "", parent);
			}
			return false;
		}

		private bool CopyObject(DbView.Command command, CopyTableModel.CopyObjectType objectType, DbViewDriver.IConnectionConfig connectionConfig, System.Windows.Forms.Form parent)
		{
			if (parent != null)
			{
				string sourceTable = command[DbView.Command.TableParameterName(0)];
				model.ObjectType = objectType;
				if (objectType != CopyTableModel.CopyObjectType.Table)
					model.IsDataWanted = false;
				CopyTablePresenter ctPresenter = new CopyTablePresenter(model, sourceTable, connectionConfig);
				CopyTableForm copyDialog = new CopyTableForm(ctPresenter, connectionConfig);
				if (copyDialog.ShowDialog(parent) == System.Windows.Forms.DialogResult.OK)
				{
					// copy done: may have to refresh the grid
					return true;
				}
			}
			// no action apparently. No need to refresh the grid.
			return false;
		}

		private void TryShowProfiler(System.Windows.Forms.Form parent)
		{
			// check whether the user can access the profiler.
			String checkPermissionSql = @"
if exists (select * from fn_my_permissions(NULL, 'SERVER') 
			where permission_name='ALTER TRACE')
			select 'Y' 
else
			select 'N';
";
			String res = Helper.GenericScalarQuery(this.dataSource.ConnectionString, checkPermissionSql, "N");
			if (res == "Y")
			{
				DbView.ConnectionInfo ci = SqlServerDataSource.InfoFromConnectionString(this.dataSource.ConnectionString);
				SqlServer.Profiler.ProfilerForm profilerWindow = new SqlServer.Profiler.ProfilerForm(this.dataSource.ConnectionString, ci.Database);
				profilerWindow.Show(parent);
				String contactSql = "select 'Hello profiler from DbViewSharp'";
				res = Helper.GenericScalarQuery(this.dataSource.ConnectionString, contactSql, "");
			}
			else
			{
				this.DoProfilerPermissionError(parent);
			}
		}

		private void DoProfilerPermissionError(System.Windows.Forms.Form parent)
		{
			const String PROFILER_HELP=@"
The current connection appears not to have permission
to access trace information on the server. It requires
ALTER TRACE permission to be granted to it. 
Either:
1. Try using a connection with more permissions.
2. Ask your DBA to grant this account ALTER TRACE permission.
3. Try to set it yourself from an admin account:
   use master
   grant ALTER TRACE to [sql login or windows NT account]
";
			String errMessage = String.Format(PROFILER_HELP);
			System.Windows.Forms.MessageBox.Show(parent,errMessage, "Could not start profiling", 
												System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
		}

		private bool CheckColumnIsOkToModify(System.Windows.Forms.Form parent, DbView.FieldInfo fieldInfo)
		{
			String message = "";
			if (fieldInfo.IsPKey)
				message = "Cannot modify a primary key";
			else
				if (fieldInfo.IsFKey)
					message = "Cannot modify a foreign key column";
			else
				if (fieldInfo.IsIndexed)
					message = "Cannot modify a column in an index";
			if (message.Length == 0) return true;

			System.Windows.Forms.MessageBox.Show(parent, message, "Cannot modify column", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
			return false;
		}

		bool ExecuteWithFeedback(String connectionString, String script, String failMessage, String confirmMessage, String confirmTitle, System.Windows.Forms.Form parent)
		{
			bool proceed = true;
			if (confirmMessage.Length > 0)
				if (!this.ConfirmDangerousOperation(parent, confirmTitle, confirmMessage))
					proceed = false;
			if (proceed)
			{
				bool isOk;
				String execMessage = Helper.GenericExecute(connectionString, script, out isOk);
				if (isOk) return true;
				System.Windows.Forms.MessageBox.Show(parent, execMessage + "\n" +script, failMessage, System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
			}
			return false;
		}

		bool ConfirmDangerousOperation(System.Windows.Forms.Form parent, String operation, String message)
		{
			System.Windows.Forms.DialogResult answer = System.Windows.Forms.MessageBox.Show(parent, message, operation, System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Warning);
			return (answer == System.Windows.Forms.DialogResult.Yes) ? true : false;
		}

		SqlServerDataSource dataSource;
		/////////////////////////////////////////////////////////////////
		// state
		// use for both copy and compare options.
		String holdCopyTargetDb = "";
		String holdObjectType = "";
		CopyTableModel model = new CopyTableModel();
	}
}
